*This file produces the results for Table 2, where we examine small-area variations in treatment, provider supply, and practice style.

********************************************************************************
*DEFINE DIRECTORIES
local home CHILD
local main CHILD/JPE
local logs CHILD/JPE/logs
local data CHILD/JPE/data
local results CHILD/JPE/results
local network NETWORK
********************************************************************************

*generate log file
cap log close
cd "`results'"
local time = subinstr(c(current_date)," ","",.)
log using table2.txt, text replace

************************************************PANEL A: Take market areas with sample children
cd "`home'"
use basis_sample.dta, clear
keep patientid zcta_kid therapy_only both drug_only
rename zcta_kid zcta_kid_basis

*create flag for JPE sample (follow 2 years) ***NOTE*** This is new JPE flag.
cd "`data'"
merge 1:1 patientid using final_jpe_sample.dta
keep if _merge==3
drop _merge

*grab treatment year
preserve
cd "`main'"
use regression_data_final_jpe.dta, clear
keep patientid year
tempfile temp
save `temp', replace
restore

merge 1:1 patientid using `temp'
keep if _merge==3
drop _merge

*order the variables
order treated therapy_only both drug_only drugs red_flag-fda_ok, after(patientid)

*define kid indicator
generate new_sample=1

*collapse into geography records
collapse (sum)  treated- fda_ok new_sample, by(zip3 year)

*calculate shares
generate one = treated/new_sample
generate two = therapy_only/treated
generate three = both/treated
generate four = drug_only/treated
generate eight = drugs/treated
generate five = red_flag/(both+drug_only)
generate six = grey_area/(both+drug_only)
generate seven = fda_ok/(both+drug_only)
foreach var of varlist one-seven {
replace `var' =0 if `var'==.
}

keep zip3 year one-seven

***LEFT SIDE OF PANEL A
*generate distributions
foreach var of varlist one-seven {
	summarize `var',d
	generate `var'_p10 = r(p10)
	generate `var'_p25 = r(p25)
	generate `var'_p50 = r(p50)
	generate `var'_p75 = r(p75)
	generate `var'_p90 = r(p90)
}

preserve
keep one_* two_* three_* four_* five_* six_* seven_* eight_*

foreach var in p10 p25 p50 p75 p90 {
	rename *`var' `var'_*
}

generate id = 1
keep if _n==1

reshape long p10 p25 p50 p75 p90, i(id) j(group) str

sort group
tempfile table
save `table', replace
restore

***RIGHT SIDE OF PANEL A
*generate distributions
summarize one-seven, d

foreach var of varlist one-seven {
egen average_`var' = mean(`var')
}

foreach var of varlist one-seven {
by zip3, s: egen average_zcta_`var' = mean(`var')
}

foreach var of varlist one-seven {
generate first_`var' = (`var'-average_`var')^2
generate second_`var' = (`var' - average_zcta_`var')^2
generate third_`var' = (average_zcta_`var'-average_`var')^2
}

foreach var of varlist one-seven {
egen double `var'_s1 = sum(first_`var')
egen double `var'_s2 = sum(second_`var')
egen double `var'_s3 = sum(third_`var')
}

foreach var of varlist one-seven {
generate `var'_total = `var'_s1/`var'_s1
generate `var'_within = `var'_s2/`var'_s1
generate `var'_between = `var'_s3/`var'_s1
}

keep *_total *_within *_between
generate id = 1
keep if _n==1

drop *total*
rename *within within_*
rename *between between_*

reshape long within_ between_, i(id) j(group) str
replace group = "_"+group

merge 1:1 group using `table'
drop _merge

sort group
order within between, after(p90)

generate id = .
replace id = 1 if group=="_one_"
replace id = 2 if group=="_two_"
replace id = 3 if group=="_three_"
replace id = 4 if group=="_four_"
replace id = 5 if group=="_five_"
replace id = 6 if group=="_six_"
replace id = 7 if group=="_seven_"
replace id = 4.5 if group=="_eight_"

sort id

generate label = ""
replace label = "Child treated within 3 months" if id==1
replace label = "Therapy only (if any treatment)" if id==2
replace label = "Drugs & therapy (if any treatment)" if id==3
replace label = "Drugs only (if any treatment)" if id==4
replace label = "Red-flag drug (if any drug treatment)" if id==5
replace label = "Grey-area drug (if any drug treatment)" if id==6
replace label = "FDA-approved drug (if any drug treatment)" if id==7
replace label = "Drugs (if any treatment)" if id==4.5

order label, first
drop group

tempfile panelA
save `panelA', replace


************************************PANEL B: Take ALL market areas at the ZCTA level
*bring in data
cd "`main'"
use market-measures.dta, clear

*merge in additional instrument
destring zcta_kid, replace
cd "`home'"
merge m:1 zcta_kid year using market-measures-antipsych.dta
drop if _merge==2
drop _merge

*parse variables
keep zcta_kid year *_pm share_mh share_psych share_gp s1_by_psych share_antipsychotic

preserve
*bring in data
cd "`main'"
use regression_data_final_jpe.dta, clear
keep zcta_kid zip3
generate count = 1
collapse (sum) count, by(zip3 zcta_kid)
destring zcta_kid, replace
tempfile temp
save `temp', replace
restore

merge m:1 zcta_kid using `temp'
keep if _merge==3
drop _merge

local outcomes psych_pm therapist_pm gp_pm share_mh share_gp share_psych s1_by_psych share_antipsychotic

collapse (mean) `outcomes' [w=count], by(zip3 year)
rename psych_pm one
rename therapist_pm two
rename gp_pm three
rename share_mh four
rename share_gp five
rename share_psych six
rename s1_by_psych seven
rename share_antipsychotic eight

***LEFT SIDE OF PANEL A
*generate distributions
foreach var of varlist one-eight {
	summarize `var',d
	generate `var'_p10 = r(p10)
	generate `var'_p25 = r(p25)
	generate `var'_p50 = r(p50)
	generate `var'_p75 = r(p75)
	generate `var'_p90 = r(p90)
}

preserve
keep one_* two_* three_* four_* five_* six_* seven_* eight_*

foreach var in p10 p25 p50 p75 p90 {
	rename *`var' `var'_*
}

generate id = 1
keep if _n==1

reshape long p10 p25 p50 p75 p90, i(id) j(group) str

sort group
tempfile table
save `table', replace
restore

drop one_p10-eight_p90

*generate distributions
summarize one-eight, d

foreach var of varlist one-eight {
egen average_`var' = mean(`var')
}

foreach var of varlist one-eight {
by zip3, s: egen average_zcta_`var' = mean(`var')
}

foreach var of varlist one-eight {
generate first_`var' = (`var'-average_`var')^2
generate second_`var' = (`var' - average_zcta_`var')^2
generate third_`var' = (average_zcta_`var'-average_`var')^2
}

foreach var of varlist one-eight {
egen double `var'_s1 = sum(first_`var')
egen double `var'_s2 = sum(second_`var')
egen double `var'_s3 = sum(third_`var')
}

foreach var of varlist one-eight {
generate `var'_total = `var'_s1/`var'_s1
generate `var'_within = `var'_s2/`var'_s1
generate `var'_between = `var'_s3/`var'_s1
}

keep *_total *_within *_between

generate id = 1
keep if _n==1

drop *total*
rename *within within_*
rename *between between_*

reshape long within_ between_, i(id) j(group) str
replace group = "_"+group

merge 1:1 group using `table'
drop _merge id


order within between, after(p90)

generate id = .
replace id = 1 if group=="_one_"
replace id = 2 if group=="_two_"
replace id = 3 if group=="_three_"
replace id = 4 if group=="_four_"
replace id = 5 if group=="_five_"
replace id = 6 if group=="_six_"
replace id = 7 if group=="_seven_"
replace id = 8 if group=="_eight_"
replace id = id+10

replace group = ""

generate label = ""
replace label = "Psychiatrists per 1,000 Insurer children 10-17" if id==11
replace label = "Therapists per 1,000 Insurer children 10-17" if id==12
replace label = "PCPs providing MH treatment per 1,000 Insurer children 10-17" if id==13
replace label = "INSURER: Share PCP's patients who receive a MH drug'" if id==14
replace label = "INSURER: Share MH providers who are PCPs" if id==15
replace label = "INSURER: Share MH providers who are psychiatrists" if id==16
replace label = "IQVIA: Share new prescriptions by psychiatrists" if id==17
replace label = "INSURER: Share DAX patients with antipsychotic" if id==18

drop group

append using `panelA'
sort id

order label, first
drop id

cd "`results'"
export excel using "table2.xlsx", firstrow(variables) replace


cap log close